package com.jeethink.web.core;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @ClassName ExcelUtils
 * @Description TODO
 * @Author Hyunjin
 * @Date 2022-08-17  17:55
 * @Version 1.0
 **/

public class ExcelUtils {
    /**
     * 获取 Excel 文件表头信息
     *
     * @param fileUrl
     * @return
     * @throws Exception
     */
    public Set<String> getExcelHeaders(String fileUrl) throws Exception {
        File file = new File(fileUrl);
        InputStream is = new FileInputStream(file);
        Workbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);
        System.out.println(sheet.getLastRowNum());
        //获取 excel 第一行数据（表头）
        Row row = sheet.getRow(1);
        //存放表头信息
        Set<String> set = new HashSet<>();
        //算下有多少列
        int colCount = sheet.getRow(1).getLastCellNum();
        System.out.println(colCount);
        for (int j = 0; j < colCount; j++) {
            Cell cell = row.getCell(j);
            String cellValue = cell.getStringCellValue().trim();
            set.add(cellValue);
        }
        return set;
    }

    public static  List<List<Map<String,String>>> readExcelWithTitle(String filepath) throws Exception{

        String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());

        InputStream is = null;

        Workbook wb = null;

        try {

            is = new FileInputStream(filepath);



            if (fileType.equals("xls")) {

                wb = new HSSFWorkbook(is);

            } else if  (fileType.equals("xlsx")) {

                wb = new XSSFWorkbook(is);

            } else {

                throw new  Exception("读取的不是excel文件");

            }



            List<List<Map<String,String>>> result = new ArrayList<List<Map<String,String>>>();//对应excel文件



            int sheetSize = wb.getNumberOfSheets();

            for (int i = 0; i < sheetSize; i++) {//遍历sheet页

                Sheet sheet = wb.getSheetAt(i);

                List<Map<String,String>> sheetList = new ArrayList<Map<String,String>>();//对应sheet页



                List<String> titles = new ArrayList<String>();//放置所有的标题



                int rowSize = sheet.getLastRowNum() + 1;

                for (int j = 0; j < rowSize; j++) {//遍历行

                    Row row = sheet.getRow(j);

                    if (row == null) {//略过空行

                        continue;

                    }

                    int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列

                    if (j == 0) {//第一行是标题行

                        for (int k = 0; k < cellSize; k++) {

                            Cell cell = row.getCell(k);

                            titles.add(cell.toString());

                        }

                    } else {//其他行是数据行

                        Map<String, String> rowMap = new HashMap<String, String>();//对应一个数据行

                        for (int k = 0; k < titles.size(); k++) {

                            Cell cell = row.getCell(k);

                            String key = titles.get(k);

                            String value = null;

                            if (cell != null) {

                                value = cell.toString();

                            }

                            rowMap.put(key, value);

                        }

                        sheetList.add(rowMap);

                    }

                }

                result.add(sheetList);

            }



            return result;

        } catch (FileNotFoundException e) {

            throw e;

        } finally {

            if (wb != null) {

                wb.close();

            }

            if (is != null) {

                is.close();

            }

        }

    }

    public static  List<List<List<String>>> readExcelWithoutTitle(String filepath) throws Exception{

        String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());

        InputStream is = null;

        Workbook wb = null;

        try {

            is = new FileInputStream(filepath);



            if (fileType.equals("xls")) {

                wb = new HSSFWorkbook(is);

            } else if  (fileType.equals("xlsx")) {

                wb = new XSSFWorkbook(is);

            } else {

                throw new  Exception("读取的不是excel文件");

            }



            List<List<List<String>>> result = new ArrayList<List<List<String>>>();//对应excel文件



            int sheetSize = wb.getNumberOfSheets();

            for (int i = 0; i < sheetSize; i++) {//遍历sheet页

                Sheet sheet = wb.getSheetAt(i);

                List<List<String>> sheetList = new ArrayList<List<String>>();//对应sheet页



                int rowSize = sheet.getLastRowNum() + 1;

                for (int j = 0; j < rowSize; j++) {//遍历行

                    Row row = sheet.getRow(j);

                    if (row == null) {//略过空行

                        continue;

                    }

                    int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列

                    List<String> rowList = new ArrayList<String>();//对应一个数据行

                    for (int k = 0; k < cellSize; k++) {

                        Cell cell = row.getCell(k);

                        String value = null;

                        if (cell != null) {

                            value = cell.toString();

                        }

                        rowList.add(value);

                    }

                    sheetList.add(rowList);

                }

                result.add(sheetList);

            }



            return result;

        } catch (FileNotFoundException e) {

            throw e;

        } finally {

            if (wb != null) {

                wb.close();

            }

            if (is != null) {

                is.close();

            }

        }

    }

    /**
     *现在传的是MultipartFile ，可以自行修改成String path
     *  当是path 时 ，使用 Workbook wb = WorkbookFactory.create(new File(path));获取
     **/
    public static List<String[]> readExcel(MultipartFile excelInput, int startrow, int sheetnum) {

        Workbook workbook = null;
        InputStream inputStream = null;
        List<String[]> list = new ArrayList<String[]>();
        try {
            inputStream = excelInput.getInputStream();

            workbook = WorkbookFactory.create(inputStream);
            //当使用路径时使用 Workbook wb = WorkbookFactory.create(new File(path));
            int numberOfSheets = workbook.getNumberOfSheets();//sheet表
            if (numberOfSheets >= sheetnum) {
                Sheet sheet = workbook.getSheetAt(sheetnum);
                if (null != sheet) {
                    // 获得当前sheet的开始行
                    //int firstRowNum = sheet.getFirstRowNum();
                    // 获得当前sheet的结束行
                    int lastRowNum = sheet.getLastRowNum();

                    for (int rowNum = startrow; rowNum <= lastRowNum; rowNum++) {
                        // 获得当前行
                        Row row = sheet.getRow(rowNum);
                        if (row == null) {
                            continue;
                        }
                        // 获得当前行的开始列
                        // int firstCellNum = row.getFirstCellNum();
                        // 获得当前行的列数
                        int lastCellNum = row.getLastCellNum();

                        String[] cells = new String[lastCellNum];
                        // 循环当前行
                        for (Cell c : row) {//获取每列数据
                            int regions = sheet.getNumMergedRegions();//是否含有合并的单元格
                            if (regions > 0) {
                                boolean isMerge = isMergedRegion(sheet, rowNum, c.getColumnIndex());
                                // 判断是否具有合并单元格
                                if (isMerge) {
                                    String value = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
                                    cells[c.getColumnIndex()] = value;
                                } else {
                                    cells[c.getColumnIndex()] = getCellValue(c);
                                }
                            } else {
                                cells[c.getColumnIndex()] = getCellValue(c);
                            }
                        }
                        list.add(cells);

                    }
                }
            }

        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            if (null != workbook) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (null != inputStream) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return list;
    }

    /**
     * 获取合并单元格的值
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param row    行下标
     * @param column 列下标
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    //读取每个单元格的数据
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字 又分为纯数字和时间
                cellValue = stringDateProcess(cell);
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "无";
                break;
        }
        return cellValue;
    }

    /**
     * 时间格式转换
     *
     * @param cell
     * @return
     */
    public static String stringDateProcess(Cell cell) {
        String result = "";
        if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
            SimpleDateFormat sdf = null;
            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                sdf = new SimpleDateFormat("HH:mm ");
            } else {// 日期
                sdf = new SimpleDateFormat("yyyy-MM-dd");
            }
            Date date = cell.getDateCellValue();
            result = sdf.format(date);
        } else if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            result = sdf.format(date);
        } else {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }
        return result;
    }




}

